﻿#region

using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using BusinessObjects;

#endregion

namespace DataAccessLayer
{
    public static class StaffDAL
    {
        private static readonly string ConnectionString =
            ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;

        /// <summary>
        ///     Register a staff
        /// </summary>
        /// <param name="staff">Represent's the staff</param>
        /// <returns>Staff id</returns>
        public static int RegisterStaff(Staff staff)
        {
            using (var connection = new SqlConnection(ConnectionString))
            {
                var command = new SqlCommand("spAddStaff", connection)
                {
                    CommandType = CommandType.StoredProcedure
                };

                command.Parameters.AddWithValue("@FirstName", staff.FirstName);
                command.Parameters.AddWithValue("@LastName", staff.LastName);
                command.Parameters.AddWithValue("@EMail", staff.EMail);
                command.Parameters.AddWithValue("@TelephoneNo", staff.TelephoneNo);
                command.Parameters.AddWithValue("@Address", staff.Address);
                command.Parameters.AddWithValue("@Type", staff.Type);
                if (staff.SubsidiaryId == null)
                {
                    command.Parameters.AddWithValue("@SubsidiaryId", DBNull.Value);
                }
                else
                {
                    command.Parameters.AddWithValue("@SubsidiaryId", staff.SubsidiaryId);
                }

                connection.Open();
                return Convert.ToInt32(command.ExecuteScalar());
            }
        }

        /// <summary>
        ///     Check whether the email is taken or not
        /// </summary>
        /// <param name="eMail">Represent's the staff's email</param>
        /// <returns>Taken or not</returns>
        public static bool IsEMailTaken(string eMail)
        {
            using (var connection = new SqlConnection(ConnectionString))
            {
                var command = new SqlCommand("spIsStaffEMailTaken", connection)
                {
                    CommandType = CommandType.StoredProcedure
                };

                command.Parameters.AddWithValue("@EMail", eMail);

                connection.Open();
                return Convert.ToBoolean(command.ExecuteScalar());
            }
        }

        /// <summary>
        ///     Get All staffs
        /// </summary>
        /// <returns>All staffs</returns>
        public static List<Staff> GetAllStaffs()
        {
            var staffs = new List<Staff>();
            using (var connection = new SqlConnection(ConnectionString))
            {
                var command = new SqlCommand("spGetAllStaffs", connection)
                {
                    CommandType = CommandType.StoredProcedure
                };

                connection.Open();
                using (var reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        var staff = new Staff
                        {
                            StaffId = Convert.ToInt32(reader["StaffId"]),
                            FirstName = reader["FirstName"].ToString(),
                            LastName = reader["LastName"].ToString(),
                            EMail = reader["EMail"].ToString(),
                            Address = reader["Address"].ToString(),
                            TelephoneNo = reader["TelephoneNo"].ToString(),
                            Type = reader["Type"].ToString()
                        };

                        if (reader["SubsidiaryId"] != DBNull.Value)
                        {
                            staff.SubsidiaryId = Convert.ToByte(reader["SubsidiaryId"]);
                        }

                        staffs.Add(staff);
                    }
                }
            }
            return staffs;
        }

        /// <summary>
        ///     Get staff by id
        /// </summary>
        /// <param name="staffId">Represent's the staff id</param>
        /// <returns>Staff</returns>
        public static Staff GetStaffById(int staffId)
        {
            using (var connection = new SqlConnection(ConnectionString))
            {
                var command = new SqlCommand("spGetStaffById", connection)
                {
                    CommandType = CommandType.StoredProcedure
                };

                command.Parameters.AddWithValue("@StaffId", staffId);

                connection.Open();
                using (var reader = command.ExecuteReader())
                {
                    if (reader.Read())
                    {
                        var staff = new Staff
                        {
                            StaffId = Convert.ToInt32(reader["StaffId"]),
                            FirstName = reader["FirstName"].ToString(),
                            LastName = reader["LastName"].ToString(),
                            EMail = reader["EMail"].ToString(),
                            Address = reader["Address"].ToString(),
                            TelephoneNo = reader["TelephoneNo"].ToString(),
                            Type = reader["Type"].ToString()
                        };

                        if (reader["SubsidiaryId"] != DBNull.Value)
                        {
                            staff.SubsidiaryId = Convert.ToByte(reader["SubsidiaryId"]);
                        }

                        return staff;
                    }
                }
            }
            return null;
        }

        /// <summary>
        ///     Edit staff details
        /// </summary>
        /// <param name="staff">Represent's the staff</param>
        public static void EditStaff(Staff staff)
        {
            using (var connection = new SqlConnection(ConnectionString))
            {
                var command = new SqlCommand("spEditStaff", connection)
                {
                    CommandType = CommandType.StoredProcedure
                };

                command.Parameters.AddWithValue("@StaffId", staff.StaffId);
                command.Parameters.AddWithValue("@FirstName", staff.FirstName);
                command.Parameters.AddWithValue("@LastName", staff.LastName);
                command.Parameters.AddWithValue("@EMail", staff.EMail);
                command.Parameters.AddWithValue("@TelephoneNo", staff.TelephoneNo);
                command.Parameters.AddWithValue("@Address", staff.Address);
                command.Parameters.AddWithValue("@Type", staff.Type);

                if (staff.SubsidiaryId == null)
                {
                    command.Parameters.AddWithValue("@SubsidiaryId", DBNull.Value);
                }
                else
                {
                    command.Parameters.AddWithValue("@SubsidiaryId", staff.SubsidiaryId);
                }

                connection.Open();
                command.ExecuteNonQuery();
            }
        }

        /// <summary>
        ///     Remove a staff
        /// </summary>
        /// <param name="staffId">Represent's the staff id</param>
        public static void RemoveStaff(int staffId)
        {
            using (var connection = new SqlConnection(ConnectionString))
            {
                var command = new SqlCommand("spRemoveStaff", connection)
                {
                    CommandType = CommandType.StoredProcedure
                };

                command.Parameters.AddWithValue("@StaffId", staffId);

                connection.Open();
                command.ExecuteNonQuery();
            }
        }
    }
}